﻿/*
 * Copyright 2009 - 2010 eScience Institute
 *
 * This file is part of SqlShare.
 *
 * SqlShare is free software: you can redistribute it and/or modify it under the terms 
 * of the GNU General Public License as published by the Free Software Foundation, 
 * either version 3 of the License, or (at your option) any later version.
 *
 * SqlShare is distributed in the hope that it will be useful, but WITHOUT ANY 
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with 
 * SqlShare. If not, see http://www.gnu.org/licenses/.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SqlShareAPI;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Westwind.Web.JsonSerializers;

namespace WebQueryRole
{
    public partial class ViewDesigner : System.Web.UI.Page
    {        
        protected bool HasViewWizard { private set; get; }

        private const string COLUMN_NAME = "column_";
        
        
        protected bool createViewSuccess
        {
            get;
            private set;
        }

        // we dont want to name our textbox sql_query, but lets put over that value if we have it
        protected string SqlQuery
        {
            get
            {
                return (!String.IsNullOrEmpty(Request["textAreaSelectQuery"])) ? Request["textAreaSelectQuery"] : Request["sql_query"];
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            SSAPIFactory fac = CloudUtil.CreateSSAPIFactory();
            UserSession session = WebAuthentication.IsLoggedIn(this, fac);
            if (session == null)
            {
                fac.Close();
                return;
            }
            try
            {
                divReport.InnerHtml = "";
                HasViewWizard = false;
                createViewSuccess = false;

                // if we have a string for SQL query, build the proper interface
                if (!String.IsNullOrEmpty(SqlQuery))
                {
                    BuildWizard(session);
                    HasViewWizard = true;
                }

                // posted the data, attempt to query
                if (IsPostBack)
                {
                    CreateView(session);
                }
            }
            catch (Exception ex)
            {
                printError(ex.Message);
            }
            fac.Close();
        }

        private void BuildWizard(UserSession session)
        {
            throw new Exception("fix this to create a view");
            // set the input textbox on the main page to this
            textAreaSelectQuery.Value = SqlQuery;

            if (!IsPostBack)
            {
                if (!String.IsNullOrEmpty(Request["view_name"]))
                {
                    inputViewName.Value = Request["view_name"].Replace(" ", "_");
                }
                else if (!String.IsNullOrEmpty(Request["hiddenViewName"]))
                {
                    inputViewName.Value = Request["hiddenViewName"];
                }
            }


            // for now assume its always a select query
            SqlCommand cmd = new SqlCommand(SqlQuery, session.ContentSqlConnection);
            HtmlTable table = new HtmlTable();


            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            int rowsFound = adapter.Fill(ds, 0, 1, "source");

            // for each column create a row
            DataTable dt = ds.Tables[0];

            // create table header
            HtmlTableRow tr = new HtmlTableRow();

            // headers
            tr.Attributes["class"] = "row_header";
            AddRow("Column Name", tr);
            AddRow("Data Type", tr);
            AddRow("Sample", tr);
            table.Rows.Add(tr);

            int columnCount = dt.Columns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                tr = new HtmlTableRow();

                tr.Attributes["class"] = (i % 2 == 0 ? "row_even" : "row_odd");

                // column 1          
                TextBox tb = new TextBox();
                tb.Text = dt.Columns[i].ColumnName;
                tb.ID = COLUMN_NAME + i;
                AddRow(tb, tr);

                // column 2
                AddRow(dt.Columns[i].DataType.ToString().Replace("System.", ""), tr);

                // column 3
                AddRow((rowsFound > 0) ? dt.Rows[0][i].ToString() : "No Data Found", tr);

                // insert row into table
                table.Rows.Add(tr);
            }

            phViewColumns.Controls.Add(table);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="content"></param>
        /// <param name="row"></param>
        private static void AddRow(string content, HtmlTableRow row)
        {
            AddRow(new LiteralControl(content), row);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ctr"></param>
        /// <param name="row"></param>
        private static void AddRow(Control ctr, HtmlTableRow row)
        {
            HtmlTableCell tc = new HtmlTableCell();
            tc.Controls.Add(ctr);
            row.Cells.Add(tc);
        }

        private void CreateView(UserSession session)
        {
            throw new Exception("fix this to reflect new API");
            // do sanity checks throw exceptions if they do not match            
            string viewName = Request["inputViewName"]; // use this intead of inputViewName.Value because we modify that value
            if (!IsValidSqlName(viewName))
            {
                throw new Exception("Invalid viewname");
            }

            string viewColumns = "";
            
            for (int columnIndex = 0; !String.IsNullOrEmpty(Request.Form[COLUMN_NAME + columnIndex]); columnIndex++)
            {
                string columnName = Request.Form[COLUMN_NAME + columnIndex];
                if (!IsValidSqlName(columnName))
                {
                    throw new Exception("Invalid Column name, must not be empty string and only contain [A-Za-z_][A-Za-z_0-9] characters: \n" + columnName);
                }
                viewColumns += columnName + ",";
            }

            // if empty string, no columns throw error
            if (String.IsNullOrEmpty(viewColumns))
            {
                throw new Exception("No columns found, aborting");
            }

            viewColumns = viewColumns.Substring(0, viewColumns.Length - 1);

            // build create view SQL string 0 = viewname, 1 = column names, 2 = select statement
            string dropifExistsViewSql = @"
 IF EXISTS(
	SELECT * FROM sys.views
	WHERE name = '" + viewName + @"')
	DROP VIEW " + viewName + "";

            string createViewSql = String.Format("CREATE VIEW {0} ({1}) AS {2}", viewName, viewColumns, SqlQuery);

            // connect to database and create new view
            SqlConnection conn = session.ContentSqlConnection;
            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;

                cmd.CommandText = dropifExistsViewSql;
                cmd.ExecuteNonQuery();

                cmd.CommandText = createViewSql;
                cmd.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (SqlException ex)
            {
                transaction.Rollback();
                throw ex;
            }

            createViewSuccess = true;

            divReport.InnerHtml = "Succsesfully Created " + viewName + ".<br/>You will be redirected to the query page in 3 seconds..";
        }

        /// <summary>
        /// Check to see if this name will be a valid SQL name for a column or table name
        /// aka, cannot be keywords, or have whitespaces or other illegal characters
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private static bool IsValidSqlName(string name)
        {
            if (String.IsNullOrEmpty(name))
            {
                return false;
            }

            Regex regex = new Regex("^[A-Za-z_][A-Za-z_0-9]*$");
            return regex.IsMatch(name);
        }

        private void printError(string error)
        {
            divReport.InnerHtml += "<span style=\"color:red;\">" + error + "</span>";
        }
    }
}
